package modele.dao;

import java.sql.SQLException;
import java.util.ArrayList;

import modele.beans.Building;
import modele.beans.Employee;
import modele.beans.Schedule;
import modele.database.IDatabase;

public class ScheduleDAO {
	private IDatabase conn;
	
	public ScheduleDAO(IDatabase conn) {
		this.conn = conn; 
	}
	
	public ArrayList<Schedule> GetSchedules() throws SQLException, ClassNotFoundException {
		conn.Connect();
		ArrayList<ArrayList<Object>> scheduleList 
		= conn.Select("SELECT STARTING_DATE, ENDING_DATE, EMPLOYEE_ID, BUILDING_ID, TASK, SCHEDULE_ID FROM SCHEDULE ORDER BY STARTING_DATE");
		
		ArrayList<Schedule> schedules = new ArrayList<Schedule>();
		
		for (int i = 0; i != scheduleList.size(); ++i) {
			ArrayList<ArrayList<Object>> employee 
			= conn.Select("SELECT LAST_NAME, FIRST_NAME, ADDRESS, PHONE, EMPLOYEE_ID FROM EMPLOYEE WHERE EMPLOYEE_ID = ?", scheduleList.get(i).get(2).toString());
			
			ArrayList<ArrayList<Object>> building 
			= conn.Select("SELECT NAME, ADDRESS, PHONE, BUILDING_ID FROM BUILDING WHERE BUILDING_ID = ?", scheduleList.get(i).get(3).toString());
			
			schedules.add(new Schedule(
					(String)scheduleList.get(i).get(0),
					(String)scheduleList.get(i).get(1),
					
					new Employee(employee.get(0).get(0).toString(), employee.get(0).get(1).toString(), 
							employee.get(0).get(2).toString(), employee.get(0).get(3).toString(), scheduleList.get(i).get(2).toString()),
							
					new Building(building.get(0).get(0).toString(), building.get(0).get(1).toString(), building.get(0).get(2).toString(), 
							scheduleList.get(i).get(3).toString()),
							
					(String)scheduleList.get(i).get(4),
					scheduleList.get(i).get(5).toString()));
		}
		
		conn.Close();
		
		return schedules;
	}
	
	public void AddSchedule(Schedule schedule) throws SQLException, ClassNotFoundException {
		conn.Connect();
		conn.Insert("INSERT INTO SCHEDULE(STARTING_DATE, ENDING_DATE, EMPLOYEE_ID, BUILDING_ID, TASK, SCHEDULE_ID) "	 
				+ " SELECT ?, ?, ?, ?, ?, schedule_seq.nextval FROM DUAL", schedule.getStartingDate(), schedule.getEndingDate(), schedule.getEmployee().getId(),
				schedule.getBuilding().getId(), schedule.getTask());
		conn.Close();
	}
	
	public void UpdateSchedule(Schedule schedule) throws SQLException, ClassNotFoundException {
		conn.Connect();
		conn.Update("UPDATE SCHEDULE " +
					" SET STARTING_DATE = ?, " +
					" ENDING_DATE = ?, " +
					" EMPLOYEE_ID = ?, " +
					" BUILDING_ID = ?, " +
					" TASK = ? " +
					" WHERE SCHEDULE_ID = ?",schedule.getStartingDate(), schedule.getEndingDate(), schedule.getEmployee().getId(),
					schedule.getBuilding().getId(), schedule.getTask(), schedule.getId());
		conn.Close();
	}
	
	public void RemoveSchedule(Schedule schedule) throws SQLException, ClassNotFoundException {
		conn.Connect();
		conn.Update("DELETE FROM SCHEDULE " +
					" WHERE SCHEDULE_ID = ? ", schedule.getId());
		conn.Close();
	}
}
